package database;

import entity.Student;
import manager.StudentManager;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Noi
 */
public class StudentManagerDB {

    private CallableStatement cstmt;
    private java.sql.Connection conn;
    private ResultSet rs;

    public StudentManagerDB(java.sql.Connection conn) {
        this.conn = conn;
    }

    public void loadData(StudentManager studentManager) {
        try {
            cstmt = conn.prepareCall("{call sp_GetStudent}");
            rs = cstmt.executeQuery();
            while (rs.next()) {
                String studentID = rs.getString("StudentID");
                String classID = rs.getString("ClassID");
                String studentName = rs.getString("StudentName");
                String father = rs.getString("Father");
                String mother = rs.getString("Mother");
                String phone = rs.getString("Phone");
                String address = rs.getString("Address");
                String provinceID = rs.getString("ProvinceID");
                Date birthDay = rs.getDate("BirthDay");
                boolean gender = rs.getBoolean("Gender");
                boolean status = rs.getBoolean("Status");
                Student student = new Student(studentID, classID, studentName, father, mother, phone, address, provinceID, birthDay, gender, status);
                studentManager.add(student);
            }
            rs.close();
            cstmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(StudentManagerDB.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void deleteStudent(Student obj) {
        try {
            cstmt = conn.prepareCall("{call sp_DeleteStudent(?)}");
            cstmt.setString(1, obj.getStudentID());
            cstmt.executeUpdate();
            cstmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(StudentManagerDB.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void insertStudent(Student obj) {
        try {
            cstmt = conn.prepareCall("{call sp_InsertStudent(?,?,?,?,?,?,?,?,?,?)}");
            cstmt.setString(1, obj.getStudentID());
            cstmt.setString(2, obj.getClassID());
            cstmt.setString(3, obj.getStudentName());
            cstmt.setString(4, obj.getFather());
            cstmt.setString(5, obj.getMother());
            cstmt.setString(6, obj.getPhone());
            cstmt.setString(7, obj.getAddress());
            cstmt.setString(8, obj.getProvinceID());
            cstmt.setDate(9, new java.sql.Date(obj.getBirthDay().getTime()));
            cstmt.setBoolean(10, obj.isGender());
            cstmt.executeUpdate();
            cstmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(StudentManagerDB.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void updateStudent(Student obj) {
        try {
            cstmt = conn.prepareCall("{call sp_UpdateStudent(?,?,?,?,?,?,?,?,?,?,?)}");
            cstmt.setString(1, obj.getStudentID());
            cstmt.setString(2, obj.getClassID());
            cstmt.setString(3, obj.getStudentName());
            cstmt.setString(4, obj.getFather());
            cstmt.setString(5, obj.getMother());
            cstmt.setString(6, obj.getPhone());
            cstmt.setString(7, obj.getAddress());
            cstmt.setString(8, obj.getProvinceID());
            cstmt.setDate(9, new java.sql.Date(obj.getBirthDay().getTime()));
            cstmt.setBoolean(10, obj.isGender());
            cstmt.setBoolean(11, obj.isStatus());
            cstmt.executeUpdate();
            cstmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(StudentManagerDB.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}
